package com.supermap.dataservice.cloud.suggest;


import com.supermap.dubbo.api.cloud.suggest.Suggest;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description 专家建议
 * @date 2018年11月26 下午 2:35
 * @email 1301584033@qq.com
 */
@Mapper
public interface SuggestMapper {

    @Select("select t2.showName,t2.HeadImage,t1.userid,t3.code as subjectCode,t3.name as subjectName,t4.name as typeName,t5.name as cropName,t5.code as cropCode,t6.id as pubId,t6.puberty as pubertyName,t1.title,t1.info,t1.areaCode,t7.areaName,date_format(t1.stime,'%Y-%m-%d %H:%i:%S') as stime from t_suggest t1\n" +
            "inner join t_user t2\n" +
            "on t1.userid=t2.id\n" +
            "inner join t_subject t3\n" +
            "on t1.subjectid=t3.code\n" +
            "inner join t_suggest_type t4\n" +
            "on t1.typeid=t4.id\n" +
            "left join t_crop t5\n" +
            "on t1.crop=t5.code\n" +
            "left join t_puberty t6\n" +
            "on t1.pubertyId=t6.id\n" +
            "inner join t_area t7\n" +
            "on t1.areacode =t7.areacode\n" +
            "where t1.del_flg=0 and t1.typeid=#{type} and (t1.areaCode=#{areaCode} or t1.areaCode=50)   order by t1.stime desc limit #{pageNum},#{pageSize}")
    List<Map<String,Object>>  getPageSuggest(@Param("areaCode")String areaCode,@Param("type")Integer type,@Param("pageNum")Integer pageNum,@Param("pageSize")Integer pageSize);


    @Select("select count(t1.id)  from t_suggest t1\n" +
            "inner join t_user t2\n" +
            "on t1.userid=t2.id\n" +
            "inner join t_subject t3\n" +
            "on t1.subjectid=t3.code\n" +
            "inner join t_suggest_type t4\n" +
            "on t1.typeid=t4.id\n" +
            "left join t_crop t5\n" +
            "on t1.crop=t5.code\n" +
            "left join t_puberty t6\n" +
            "on t1.pubertyId=t6.id\n" +
            "inner join t_area t7\n" +
            "on t1.areacode =t7.areacode\n" +
            "where t1.del_flg=0 and t1.typeid=#{type} and (t1.areaCode=#{areaCode} or t1.areaCode=left(#{areaCode},2))   order by t1.stime desc limit #{pageNum},#{pageSize}")
    Integer  getPageSuggestCountNum(@Param("areaCode")String areaCode,@Param("type")Integer type,@Param("pageNum")Integer pageNum,@Param("pageSize")Integer pageSize);
    @Select("select id,title,info from t_suggest  where   typeid =7 and  (find_in_set(#{areaCode},areacode)  or areacode = 50)  and del_flg =0 order by stime desc limit 0,1")
    List<Map<String,Object>> getSuggestTileTop1(@Param("areaCode")String areaCode);
    /**
     * 查询出作物相关的农田id
     * */
    @SelectProvider(type = SuggestMapperSQL.class,method = "getFarmIdsBycropSQL")
    List<Integer> getFarmIdsBycrop(@Param("areaCodes")String areaCodes,@Param("cropId")String cropId);

    @Insert("insert into t_suggest_farm(suggestId,farm) values(#{suggestId},#{farm}) ")
    int insertSuggestFarm(@Param("suggestId") Integer suggestId,@Param("farm")Integer farm);
    /**
     * 查询区域相关的userID
     * */
    @SelectProvider(type = SuggestMapperSQL.class,method = "getUserIdByAreaCodesSQL")
    List<Integer>  getUserIdByAreaCodes(@Param("areaCodes") String areaCodes);

    /**
     * 查询区域下面的农田
     * */
    @SelectProvider(type = SuggestMapperSQL.class,method = "getFarmIdByAreaCodesSQL")
    List<Integer>  getFarmIdByAreaCodes(@Param("areaCodes")String areaCodes,@Param("cropId")String cropId);

    @Select("select id,name from t_suggest_type")
    List<Map<String,Object>> getSuggestTypeList();

    /**
     *获取最新建议Id
     * */
    @Select("SELECT LAST_INSERT_ID()")
    int getSuggestId();
    /**
     * 获取门户网站url
     * */
    @Select("select t3.code ,case t3.code     \t\t\t \n" +
            "\t\t\t  when \"ZZDH\" then (select url from t_grower where userid=t1.id) \n" +
            "\t        when \"EXPERT\" then (select url from t_expertinfo where userid=t1.id)  \n" +
            "\t\t\t  when \"QYYH\" then (select url from t_company where userid=t1.id)     \n" +
            "\t\t\t  when \"HZSYH\" then (select url from t_cooperative where userid=t1.id)    \n" +
            "\t\t\t  when \"GXSYH\" then (select url from t_suply_point where userid=t1.id)\n" +
            "\t\t\t  when \"ZMXC\" then (select url from t_countryside where userid=t1.id)  \n" +
            "\t\t  end   as url  \n" +
            "from (select id from  t_user where id=#{userId}) t1\n" +
            "inner join t_user_role t2 \n" +
            "on t1.id =t2.userid \n" +
            "inner join t_role t3 on t2.roleid=t3.id;")
    List<Map<String,Object>> getinterUrlByUserId(@Param("userId")String userId);
    /**
     * 专家建议上传
     * */
    @InsertProvider(type =SuggestMapperSQL.class,method = "putSuggestSQL")
    int putSuggest(@Param("suggest")Suggest suggest);

    /**
     * 获取建议列表
     * */
    @SelectProvider(type =SuggestMapperSQL.class,method ="getPageSuggestListSQL")
    List<Map<String,Object>>  getPageSuggestList(@Param("userId") String userId,@Param("farmId")String farmId,@Param("expertUserId")String expertUserId,@Param("suggestId")String suggestId,@Param("pageNum")Integer pageNum,@Param("pageSize")Integer pageSize,@Param("keyName")String keyName,@Param("expertID")String expertID);

    /**
     * 获取建议列表
     * */
    @SelectProvider(type =SuggestMapperSQL.class,method ="getPageSuggestListCountSQL")
    int  getPageSuggestListCount(@Param("userId") String userId,@Param("farmId")String farmId,@Param("expertUserId")String expertUserId,@Param("suggestId")String suggestId,@Param("keyName")String keyName,@Param("expertID")String expertID);

    /**
     * 点赞
     * */
    @Insert("insert into t_suggest_praise(suggestId,userId) values(#{suggestId},#{userId})")
    int putPraiseSuggestList(@Param("userId") String userId,@Param("suggestId")String suggestId);

    /**
     * 根据用户Id查询专家列表
     */
    @Select("select t.noticetitle as title ,t.messageid as productId ,date_format(t.createtime,'%Y-%m-%d %H:%i:%S') as createtime from" +
            " t_notice t where t.noticetype = #{type} and t.receiveuserid = #{userId} " +
            " order by t.createtime desc limit #{currentIndex}, #{pageSize}")
    List<Map<String , Object>> getPageSuggestByUserId(@Param("userId") String userId ,@Param("type") Integer type, @Param("currentIndex")Integer currentIndex , @Param("pageSize") Integer pageSize);

    /**
     * @param productId 根据id获取产品详情
     */
    @Select("select u.username as publisher,sg.title , sg.info , date_format(sg.stime,'%Y-%m-%d %H:%i:%S') as pulishTime from t_suggest sg , t_user u  where sg.userid = u.id and sg.id = #{productId}")
    Map<String , Object> getSuggestDetails(@Param("productId") Integer productId);

    /**
     * 查询用户收到专家产品总数
     * @param userId 用户Id
     * @param type 产品类型
     */
    @Select("select count(1) from" +
            " t_notice t where t.noticetype = #{type} and t.receiveuserid = #{userId} " )
    int getSuggestCountByUserId(@Param("userId") String userId , @Param("type") Integer type);

    /**
     * 取消点赞
     * */
    @Delete("delete from t_suggest_praise where suggestId=#{suggestId} and userId=#{userId}")
    int delPraiseSuggestList(@Param("userId") String userId,@Param("suggestId")String suggestId);

    class  SuggestMapperSQL{
        public String getFarmIdsBycropSQL(Map<String,Object> map){
            String areaCodes=(String)map.get("areaCodes");
            String cropId=(String)map.get("cropId");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("select t1.id from t_farmland t1\n" +
                    "inner join t_farmcrops t2\n" +
                    "on t1.id=t2.farmland ");
            if(areaCodes!=null&&areaCodes.length()==2){
                stringBuilder.append(" and t1.code like concat("+areaCodes+",'%') ");
            }else{
                stringBuilder.append(" and find_in_set(t1.code,'"+areaCodes+"') ");
            }
            stringBuilder.append(" inner join t_crop t3\n" +
                    "on t2.crop =t3.code and t3.code="+cropId+" group by t1.id ");
            return  stringBuilder.toString();
        }

        public String getUserIdByAreaCodesSQL(Map<String,Object> map){
            String areaCodes=(String)map.get("areaCodes");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("select t1.id from t_user t1  where 1=1 ");

            if(areaCodes!=null&&areaCodes.length()==2){
                stringBuilder.append(" and t1.areaCode like concat("+areaCodes+",'%') ");
            }else{
                stringBuilder.append(" and find_in_set(t1.areaCode,'"+areaCodes+"')");
            }
            return  stringBuilder.toString();
        }
        public String getFarmIdByAreaCodesSQL(Map<String,Object> map){
            String areaCodes=(String)map.get("areaCodes");
            String cropId=(String)map.get("cropId");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("select t1.userId from t_farmland t1\n" +
                    "inner join t_farmcrops t2\n" +
                    "on t1.id=t2.farmland ");
            if(areaCodes!=null&&areaCodes.length()==2){
                stringBuilder.append(" and t1.code like concat("+areaCodes+",'%') ");
            }else{
                stringBuilder.append(" and find_in_set(t1.code,'"+areaCodes+"') ");
            }
            stringBuilder.append(" inner join t_crop t3\n" +
                    "on t2.crop =t3.code and t3.code="+cropId+" group by t1.userId ");
            return  stringBuilder.toString();

        }

        /**
         * 查询
         * */
        public String getPageSuggestListSQL(Map<String,Object> map){
            String keyName=(String)map.get("keyName");
            String userId=(String)map.get("userId");
            String farmId=(String)map.get("farmId");
            String expertUserId=(String)map.get("expertUserId");
            String suggestId=(String)map.get("suggestId");
            String expertID=(String)map.get("expertID");

            Integer pageNum=(int)map.get("pageNum");
            Integer pageSize=(int)map.get("pageSize");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("select t1.id,t1.userid,t2.showName,t2.HeadImage,t4.name as subjectName,t4.code as subjectCode,\n" +
                    "t5.id as cropId,t5.name as cropName,\n" +
                    "t3.id as pubertyId ,t3.puberty as pubertyName,\n" +
                    "t10.id as typeId,t10.name as typeName,t1.title,t1.info,\n" +
                    "date_format(t1.stime,'%Y-%m-%d %H:%i:%S') as stime,");
            stringBuilder.append( " (select group_concat(areaname) from t_area where find_in_set(areacode,t1.areacode))  as areaNames, ");
            if(userId!=null){
                stringBuilder.append("(select count(1) from t_suggest_praise where suggestId=t1.id and userId="+userId+") as isPraise,");
            }else{
                stringBuilder.append(" 0 as isPraise, ");
            }
            stringBuilder.append(" (select count(1) from t_suggest_praise where suggestId=t1.id) as praiseSumNum\n" +
                    "from t_suggest t1\n" +
                    "inner join t_user t2 \n" +
                    "on t1.userid=t2.id  and t2.del_flg=0 and t1.del_flg=0 ");

            if(suggestId!=null){
                stringBuilder.append(" and t1.id="+suggestId+" ");
            }

            if(expertUserId!=null){
                stringBuilder.append(" and t1.userid="+expertUserId+" ");
            }
            stringBuilder.append("    inner join t_expertinfo t90 on t1.userid=t90.userid ");
            if(expertID!=null){
                stringBuilder.append(" and t90.id="+expertID+"");
            }
            stringBuilder.append(" left join t_puberty t3\n" +
                    "on  t1.pubertyId =t3.id\n" +
                    "left join t_subject t4\n" +
                    "on t1.subjectid=t4.code\n" +
                    "Left join t_crop t5\n" +
                    "on t1.crop=t5.code ");
            stringBuilder.append(" left join t_suggest_type t10 on t1.typeid=t10.id ");
            if(farmId!=null){
                stringBuilder.append("inner join t_suggest_farm t6\n" +
                        "on t1.id=t6.suggestId and  t6.farm ="+farmId+" ");
            }
            if(keyName!=null){
                stringBuilder.append(" where (t5.name like concat('%','"+keyName+"','%') or t1.title like concat('%','"+keyName+"','%') or t1.info like concat('%','"+keyName+"','%') ) ");
            }
            stringBuilder.append(" order by  t1.stime desc\n" +
                    "limit "+pageNum+","+pageSize+" ");

            return  stringBuilder.toString();

        }
        public String getPageSuggestListCountSQL(Map<String,Object> map){
            String keyName=(String)map.get("keyName");
            String userId=(String)map.get("userId");
            String farmId=(String)map.get("farmId");
            String expertUserId=(String)map.get("expertUserId");
            String suggestId=(String)map.get("suggestId");
            String expertID=(String)map.get("expertID");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("select ");
            stringBuilder.append(" count(1) " +
                    "from t_suggest t1\n" +
                    "inner join t_user t2 \n" +
                    "on t1.userid=t2.id  and t2.del_flg=0 and t1.del_flg=0 ");

            if(suggestId!=null){
                stringBuilder.append(" and t1.id="+suggestId+" ");
            }

            if(expertUserId!=null){
                stringBuilder.append(" and t1.userid="+expertUserId+" ");
            }
            stringBuilder.append("    inner join t_expertinfo t90 on t1.userid=t90.userid ");
            if(expertID!=null){
                stringBuilder.append(" and t90.id="+expertID+"");
            }
            stringBuilder.append(" left join t_puberty t3\n" +
                    "on  t1.pubertyId =t3.id\n" +
                    "left join t_subject t4\n" +
                    "on t1.subjectid=t4.code\n" +
                    "Left join t_crop t5\n" +
                    "on t1.crop=t5.code ");
            if(farmId!=null){
                stringBuilder.append("inner join t_suggest_farm t6\n" +
                        "on t1.id=t6.suggestId and  t6.farm ="+farmId+" ");
            }
            if(keyName!=null){
                stringBuilder.append(" where (t5.name like concat('%','"+keyName+"','%') or t1.title like concat('%','"+keyName+"','%') or t1.info like concat('%','"+keyName+"','%') ) ");
            }
            stringBuilder.append(" order by  t1.stime desc ");

            return  stringBuilder.toString();

        }
        /**
         * 上传
         * */
        public String putSuggestSQL(Map<String,Object> map){
            Suggest suggest=(Suggest)map.get("suggest");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("insert into t_suggest ");
            StringBuilder title = new StringBuilder();
            StringBuilder val = new StringBuilder();
            if (suggest.getUserid()!=0) {
                title.append("userid,");
                val.append("" + suggest.getUserid() + ",");
            }
            if (suggest.getSubjectid() != null&&suggest.getSubjectid()!="") {
                title.append("subjectid,");
                val.append("'" + suggest.getSubjectid() + "',");
            }
            if (suggest.getTypeid() != 0) {
                title.append("typeid,");
                val.append("" + suggest.getTypeid() + ",");
            }
            if (suggest.getCrop() != 0) {
                title.append("crop,");
                val.append("" + suggest.getCrop() + ",");
            }
            if (suggest.getPubertyId() != 0) {
                title.append("pubertyId,");
                val.append("" + suggest.getPubertyId() + ",");
            }
            if (suggest.getTitle() != null&&suggest.getTitle()!="") {
                title.append("title,");
                val.append("'" + suggest.getTitle() + "',");
            }
            if (suggest.getInfo() != null&&suggest.getInfo()!="") {
                title.append("info,");
                val.append("'" + suggest.getInfo() + "',");
            }
            if (suggest.getAreacode() != null&&suggest.getAreacode()!="") {
                title.append("areacode,");
                val.append("'" + suggest.getAreacode() + "',");
            }
            return   stringBuilder.append("(" + title.substring(0, title.lastIndexOf(",")) + ") values(" + val.substring(0, val.lastIndexOf(",")) + ")").toString();

        }

    }

}
